package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.RiskEnterpriseDataRepository;
import com.css.fxfzypg.modules.baseData.entity.RiskEnterpriseData;
import com.css.fxfzypg.modules.baseData.queryParams.RiskEnterpriseDataQueryParams;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class RiskEnterpriseDataRepositoryImpl implements RiskEnterpriseDataRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Autowired
    SysAreaService sysAreaService;
//    @Override
//    public RiskEnterpriseDataVO updateElevation(RiskEnterpriseDataVO riskEnterpriseDataVO) {
//
//        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '"+elevationVO.getElevationId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return elevationVO;
//    }

    @Override
    public RiskEnterpriseData saveRiskEnterpriseData(RiskEnterpriseData riskEnterpriseData) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                 sql = "insert into sys_riskenterprisedata(red_longitude,red_latitude,red_name,red_address,red_type,red_scale,red_riskgrade,red_status,red_createtime) values(?,?,?,?,?,?,?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String [] {"red_id"});
                ps.setFloat(1, riskEnterpriseData.getRedLongitude());
                ps.setFloat(2, riskEnterpriseData.getRedLatitude());
                ps.setString(3, riskEnterpriseData.getRedName());
                ps.setString(4, riskEnterpriseData.getRedAddress());
                ps.setString(5, riskEnterpriseData.getRedType());
                ps.setString(6, riskEnterpriseData.getRedScale());
                ps.setString(7, riskEnterpriseData.getRedRiskgrade());
                ps.setString(8, riskEnterpriseData.getRedStatus());
                java.sql.Date createDate = new java.sql.Date(riskEnterpriseData.getRedCreatetime().getTime());
                ps.setDate(9,  createDate);
                return ps;
            }
        }, keyHolder);
        int i = keyHolder.getKey().intValue();
        riskEnterpriseData.setRedId(i);
        return riskEnterpriseData;
    }

    @Override
    public Page<RiskEnterpriseData> getAllRiskEnterpriseDataParams(RiskEnterpriseDataQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  r.* from sys_riskenterprisedata r ");

        StringBuilder orderSql = new StringBuilder(" order by r.red_id");

        StringBuilder whereSql = new StringBuilder(" where r.red_status = '" + YNEnum.Y.toString() + "'");
        StringBuilder str=new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for(Map roleMap:roleMaps){
            str.append(roleMap.get("roleCode")+",");
        }
        String roles = str.toString();
        if(roles.contains(FxfzConstants.YPG_PRI_YW_ROLE) || roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)  && !roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)){
            String province = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            whereSql.append(" and province = '"+province+"'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getRedName()) ){
            whereSql.append(" and r.red_name like '%" + queryParams.getRedName() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getRedAddress()) ){
           whereSql.append(" and r.red_address like '%" + queryParams.getRedAddress() + "%'");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<RiskEnterpriseData> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<RiskEnterpriseData> riskEnterpriseDatas = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return riskEnterpriseDatas;
    }

    @Override
    public RiskEnterpriseData getRiskEnterpriseDataById(Integer id) {
        String sql = "select * from sys_riskenterprisedata where red_id = '"+id+"'";
        RiskEnterpriseData riskEnterpriseData = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
        return riskEnterpriseData;
    }

    @Override
    public void deleteRiskEnterpriseDatasById(List<Integer> ids, Timestamp timestamp) {
        String sql = "update sys_riskenterprisedata set red_status = '" + YNEnum.N + "' , red_updatetime = '"+timestamp+"'" + " where red_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<RiskEnterpriseData> getAll() {
        return jdbcTemplate.query("select * from sys_riskenterprisedata",new BeanPropertyRowMapper<>(RiskEnterpriseData.class));
    }

    @Override
    public void importExcel(List<RiskEnterpriseData> riskEnterpriseData) {
        jdbcTemplate.batchUpdate("insert into sys_riskenterprisedata (red_longitude,red_latitude,red_name,red_address,red_type,red_scale,red_riskgrade,red_status,red_createtime)values(?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return riskEnterpriseData.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1, riskEnterpriseData.get(i).getRedLongitude());
                ps.setFloat(2,riskEnterpriseData.get(i).getRedLatitude());
                ps.setString(3,riskEnterpriseData.get(i).getRedName());
                ps.setString(4,riskEnterpriseData.get(i).getRedAddress());
                ps.setString(5,riskEnterpriseData.get(i).getRedType());
                ps.setString(6,riskEnterpriseData.get(i).getRedScale());
                ps.setString(7,riskEnterpriseData.get(i).getRedRiskgrade());
                ps.setString(8,riskEnterpriseData.get(i).getRedStatus());
                java.sql.Date stimeDate = new java.sql.Date(riskEnterpriseData.get(i).getRedCreatetime().getTime()) ;
                ps.setDate(9,stimeDate);
            }
        });
    }

    @Override
    public List<RiskEnterpriseData> getriskData(String taskId) {
        String sql = "SELECT province,city,county,red_name,red_address,red_type,red_riskgrade FROM sys_riskenterprisedata t " +
                "WHERE ST_Intersects(t.geom,(select geom from ypg_preassess_task where id= ? )) and red_status = '" + YNEnum.Y + "' and sfzdyh='1'";
        List<RiskEnterpriseData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RiskEnterpriseData.class),taskId);
        return list;
    }
    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_riskenterprisedata where county = '" + county + "' and city = '" + city + "' and province = '" + province + "' or (province ='"+province+"' and city ='' and county ='')", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_riskenterprisedata where city = '" + city + "' and province = '" + province + "'", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_riskenterprisedata where province = '" + province + "'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_riskenterprisedata where province = ? and (TO_CHAR(red_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(red_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?)", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
